package com.cheneyjoo.usecase;

import com.cheneyjoo.model.CLASS_STUDENT;
import com.cheneyjoo.model.STUDENT;
import com.cheneyjoo.service.IStudentService;
import com.cheneyjoo.tools.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.*;

public class StudentUseCase implements IStudentService {
    private final QueryRunner qr;

    public StudentUseCase() {
        qr = new QueryRunner(DruidUtils.DATASOURCE);
    }

    /**
     * 新增学生信息
     *
     * @param student 学生对象
     * @return 是否成功
     */
    @Override
    public boolean addStudent(STUDENT student) {
        int i = 0;
        try {
            String existsSql = "select count(1) from STUDENT where STUDENT_NO=?";
            Long count = qr.query(existsSql, new ScalarHandler<Long>(), student.getStudent_no());
            if (count > 0) {
                return false;
            }
            String insertSql = "INSERT INTO STUDENT(`ID`,`STUDENT_NO`,`STUDENT_NAME`,`STUDENT_SEX`,`STUDENT_BIRTH`,`STUDENT_EMAIL`,`STUDENT_DESC`)VALUES(NULL,?,?,?,?,?,?)";
            Object[] param = new Object[]{student.getStudent_no(), student.getStudent_name(), student.getStudent_sex(), student.getStudent_birth(), student.getStudent_email(), student.getStudent_desc()};
            i = qr.update(insertSql, param);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i > 0;
    }

    /**
     * 修改学生信息
     *
     * @param student 学生对象
     * @return
     */
    @Override
    public boolean updateStudent(STUDENT student) {
        int i = 0;
        try {
            String existsSql = "select count(1) from STUDENT where ID=?";
            Long count = qr.query(existsSql, new ScalarHandler<Long>(), student.getId());
            if (count > 0) {
                String insertSql = "UPDATE STUDENT SET STUDENT_NAME=?,STUDENT_SEX=?,STUDENT_BIRTH=?,STUDENT_EMAIL=?,STUDENT_DESC=? where ID=?";
                Object[] param = new Object[]{student.getStudent_name(), student.getStudent_sex(), student.getStudent_birth(), student.getStudent_email(), student.getStudent_desc(), student.getId()};
                i = qr.update(insertSql, param);
            } else {
                return false;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i > 0;
    }

    /**
     * 删除学生信息
     *
     * @param ids 学生ID
     * @return 是否成功
     */
    @Override
    public boolean deleteStudent(String ids) {
        int i = 0;
        try {
            String sql = "DELETE FROM STUDENT WHERE ID in(" + ids + ")";
            i = qr.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i > 0;
    }

    /**
     * 获取学生信息
     *
     * @param id 学生ID
     * @return
     */
    @Override
    public STUDENT getStudent(int id) {
        STUDENT student = null;
        try {
            String SQL = "SELECT * FROM STUDENT WHERE ID=?";
            student = qr.query(SQL, new BeanHandler<STUDENT>(STUDENT.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return student;
    }

    /**
     * 获取学生列表
     *
     * @param pageSize
     * @param currPage
     * @param where    where条件键值队
     * @return
     */
    @Override
    public Map<String, Object> getStudents(int pageSize, int currPage, Map<String, String> where) {
        StringBuilder sql = new StringBuilder();
        StringBuilder sqlCount = new StringBuilder();
        sql.append("SELECT a.*,b.CLASS_NAME as className,b.GRADE ");
        sqlCount.append("SELECT count(1) ");

        StringBuilder otherSql = new StringBuilder();
        otherSql.append(" FROM STUDENT a" +
                "   LEFT JOIN STUDENT_CLASS b ON a.CLASS_ID=b.ID      " +
                "   WHERE 1=1 ");

        List<String> param = new ArrayList<>();
        //拼接查询条件
        if (where.containsKey("no") && Optional.ofNullable(where.get("no")).isPresent()) {
            otherSql.append(" and STUDENT_NO like ? ");
            param.add("%" + where.get("no") + "%");
        }

        if (where.containsKey("name") && Optional.ofNullable(where.get("name")).isPresent()) {
            otherSql.append(" and STUDENT_NAME like ? ");
            param.add("%" + where.get("name") + "%");
        }

        if (where.containsKey("sex") && Optional.ofNullable(where.get("sex")).isPresent()) {
            otherSql.append(" and STUDENT_SEX = ? ");
            param.add(where.get("sex"));
        }
        //拼接条件
        sqlCount.append(otherSql.toString());
        sql.append(otherSql.toString());

        //拼接排序
        sql.append(" order by id desc");
        //拼接分页
        sql.append("  limit ").append((currPage - 1) * pageSize).append(",").append(pageSize);

        Long queryCount = 0L;
        List<CLASS_STUDENT> studentList = null;
        try {
            queryCount = qr.query(sqlCount.toString(), new ScalarHandler<Long>(), param.toArray());
            studentList = qr.query(sql.toString(), new BeanListHandler<CLASS_STUDENT>(CLASS_STUDENT.class), param.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //计算共有多少页
        int totalPage = 0;
        totalPage = Double.valueOf(Math.ceil(queryCount * 1.0 / pageSize * 1.0)).intValue();
        totalPage = totalPage == 0 ? 1 : totalPage;

        if (totalPage < currPage) {
            currPage = Math.toIntExact(totalPage);
        }
        Map<String, Object> result = new HashMap<>();
        result.put("table", studentList);
        result.put("totalRow", queryCount);
        result.put("totalPage", totalPage);
        return result;
    }

    @Override
    public List<STUDENT> checkStudentHaveClass(String ids) {
        List<STUDENT> studentList = null;
        try {
            String sql = "SELECT * FROM STUDENT WHERE ID in(" + ids + ") and CLASS_ID=0";
            studentList = qr.query(sql, new BeanListHandler<>(STUDENT.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return studentList;
    }
}
